package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Model.Course;
import Util.ConnectionUtil;

public class CourseDao {
	/*
	 * 查询课程列表
	 */
	public static ArrayList<Course> CourseList() {
		ArrayList<Course> CourseList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;
		try {
			// 执行查询
			System.out.println(" 实例化Statement对象...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT * FROM Course";
			ResultSet rs = stmt.executeQuery(sql);
			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				String Coursename = rs.getString("Coursename");
				int Courseid = rs.getInt("Courseid");
				Course course = new Course(Courseid, Coursename);
				CourseList.add(course);
			}
		} catch (SQLException se) {
			// ���� JDBC ����
			se.printStackTrace();
		} catch (Exception e) {
			// ���� Class.forName ����
			e.printStackTrace();
		}
		System.out.println("查询成功!");
		return CourseList;

	}

	public static void addUsePrepareStatement(Course course) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into course values(?,?)";
		try {
			PreparedStatement psta = conn.prepareStatement(sql);
			psta.setInt(1, course.getCourseid());
			psta.setString(2, course.getCoursename());

			int result = psta.executeUpdate();
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void del(String coursename) {
		Connection conn = ConnectionUtil.getConnection();

		String sql = "delete from course where Coursename = '" + coursename + "'";

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void update(String Coursename, int column, String value) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = null;
		switch (column) {
		case 1:
			sql = "update course SET Courseid = '" + value + "'  where Coursename = '" + Coursename + "'";
			break;

		case 2:
			sql = "update course SET Coursename = '" + value + "'  where Coursename = '" + Coursename + "'";
			break;

		default:
			break;
		}

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static ArrayList<Course> queryCourseList() {
		ArrayList<Course> courseList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		String sql = "select * from course";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);

			while (rs.next()) {
				int Courseid = rs.getInt(1);
				String Coursename = rs.getString(2);

				Course course = new Course(Courseid, Coursename);
				courseList.add(course);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return courseList;
	}
}
